﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace CreateReport
{
    public partial class ModifyDataForm : Form
    {
        SqlCeConnection sqlcecon= new SqlCeConnection();
        private string dbpath;
        private string holeName;
        
        public ModifyDataForm()
        {
            InitializeComponent();
        }
   
        public ModifyDataForm(string tempPah)
        {
            InitializeComponent();
            this.dbpath = tempPah;
        }

        private void ModifyDataForm_Load(object sender, EventArgs e)
        {
            sqlcecon.ConnectionString = dbpath+"Password = zzjnzlsj;";
            sqlcecon.Open();
            if (!(sqlcecon.State == ConnectionState.Open))
            {
                MessageBox.Show("连接数据库失败!");
                return;
            }
            GetBeamsNum();
            dgv_data.AutoGenerateColumns = false;
        }

        private void ModifyDataForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (sqlcecon.State == ConnectionState.Open)
            {
                sqlcecon.Close();
            }
            this.Dispose();
        }

        private DataTable GetDataTable(string selectString)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectString, sqlcecon);
                SqlCeCommandBuilder scb = new SqlCeCommandBuilder(adapter);
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlCeException)
            {
                return null;
            }
            catch (ArgumentException)
            {
                return null;
            }
        }

        private void GetBeamsNum()
        {
            string selectString = "select 梁号 from Beams";
            DataTable dt = GetDataTable(selectString);
            if (dt != null && dt.Rows.Count > 0)
            {
                cb_beams.Items.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cb_beams.Items.Add(dt.Rows[i]["梁号"].ToString());
                }
            }
            return;
        }
        private void GetBeamsNum(DateTime start,DateTime end)
        {
            string selectString = "select 梁号 from Beams where 张拉日期 between '" + start.AddDays(-1).ToShortDateString() + "' and '" + end.AddDays(1).ToShortDateString() + "'";
            DataTable dt = GetDataTable(selectString);
            if (dt != null && dt.Rows.Count > 0)
            {
                cb_beams.Items.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cb_beams.Items.Add(dt.Rows[i]["梁号"].ToString());
                }
            }

        }
        private void GetStressingTimes(string beamNum)
        {
            string selectString = "select 序号 from BeamInformation where 梁号 = '" + beamNum + "'";
            DataTable dt = GetDataTable(selectString);
            if (dt != null && dt.Rows.Count > 0)
            {
                cb_sercal.Items.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cb_sercal.Items.Add(i+1);
                }
            }
        }
        private void GetStationName(string beamNum)
        {
            int[] stations = new int[4] { 1, 2, 3, 4 };
            string selectString = "select 站号 from Calib where 梁号 = '" + beamNum + "'";
            DataTable dt = GetDataTable(selectString);
            if (dt != null && dt.Rows.Count > 0)
            {
                cb_station.Items.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cb_station.Items.Add(stations[i]);
                }
            }
        }
        private void DisplaySerachData()
        {
            try
            {
                string selectString = "select * from [" + cb_beams.Text + "_KeyValue] where 站号 = " + Convert.ToInt32(cb_station.Text) + " and 张拉序号 = " + (Convert.ToInt32(cb_sercal.Text) - 1) + "and 比例 != 0";
                DataTable dt = GetDataTable(selectString);
                if (dt != null && dt.Rows.Count > 0)
                {
                    dgv_data.DataSource = null;
                    dgv_data.DataSource = dt;
                    holeName = dt.Rows[0]["孔道"].ToString();
                    // dgv_data.Columns[0].DataPropertyName = "站号";
                    //  dgv_data.Columns[1].DataPropertyName = "张拉序号";
                    // dgv_data.Columns[2].DataPropertyName = "孔道";
                    dgv_data.Columns[0].DataPropertyName = "比例";
                    dgv_data.Columns[1].DataPropertyName = "张拉力";
                    dgv_data.Columns[2].DataPropertyName = "伸长量";

                }
            }
            catch (FormatException)
            {
            }


        }
        private bool UpdateSelectedData(string beam,int serial,string station,int scale , double power,double length)
        {
            string updateString = "update [" + beam + "_KeyValue] set 比例 = " + scale + " , 张拉力 = " + power + ",伸长量 = " + length + " where 站号 = '" + station + "' and 张拉序号 =" + serial + " and 比例 = " + scale;
            SqlCeCommand cecmd = new SqlCeCommand(updateString, sqlcecon);
            int res =  cecmd.ExecuteNonQuery();
            if (res > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        private bool InsertNewData(string beam, int serial, string station, int scale, double power, double length)
        {
            string insertStr = "Insert Into ["+beam+"_KeyValue] (站号,张拉序号,孔道,张拉力,伸长量,张拉时间,比例) values ("+station+","+serial+",'"+holeName+"',"+power+","+length+",0,"+scale+")";
            SqlCeCommand cecmd = new SqlCeCommand(insertStr, sqlcecon);
            int res = cecmd.ExecuteNonQuery();
            if (res > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        private bool DeleteData(string beam, int serial, string station, int scale)
        {
            string deleteStr = "delete from [" + beam + "_KeyValue] where 站号 = " + station + "and 张拉序号 = " + serial + "and 比例 = " + scale;
            SqlCeCommand cecmd = new SqlCeCommand(deleteStr, sqlcecon);
            int res = cecmd.ExecuteNonQuery();
            if (res > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        private void cb_beams_SelectedIndexChanged(object sender, EventArgs e)
        {
            GetStressingTimes(cb_beams.Text);
            GetStationName(cb_beams.Text);
            //DisplaySerachData();
        }

        private void dtp_start_ValueChanged(object sender, EventArgs e)
        {
            GetBeamsNum(dtp_start.Value, dtp_end.Value);
        }

        private void dtp_end_ValueChanged(object sender, EventArgs e)
        {
            GetBeamsNum(dtp_start.Value, dtp_end.Value);
        }

        private void cb_sercal_SelectedIndexChanged(object sender, EventArgs e)
        {
            DisplaySerachData();
            dgv_data.Focus();
        }

        private void cb_station_SelectedIndexChanged(object sender, EventArgs e)
        {
            DisplaySerachData();
            dgv_data.Focus();
        }

        private void dgv_data_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int index = e.RowIndex;
            string scale = dgv_data.Rows[index].Cells[0].Value.ToString();
            string power = dgv_data.Rows[index].Cells[1].Value.ToString();
            string length = dgv_data.Rows[index].Cells[2].Value.ToString();
            tb_Power.Text = power;
            tb_length.Text = length;
            tb_scale.Text = scale;
        }

        private void btn_modify_Click(object sender, EventArgs e)
        {
            DialogResult reDialog = MessageBox.Show("是否修改数据？", "", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
            if (reDialog == System.Windows.Forms.DialogResult.Yes)
            {
                bool result = UpdateSelectedData(cb_beams.Text, Convert.ToInt32(cb_sercal.Text) - 1, cb_station.Text, Convert.ToInt32(tb_scale.Text), Convert.ToDouble(tb_Power.Text), Convert.ToDouble(tb_length.Text));
                if (result)
                {
                    MessageBox.Show("修改成功!");
                    DisplaySerachData();
                }
                else
                {
                    MessageBox.Show("修改失败!");
                }
            }
        }

        private void btn_insert_Click(object sender, EventArgs e)
        {
            DialogResult reDialog = MessageBox.Show("是否添加数据？", "", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
            if (reDialog == System.Windows.Forms.DialogResult.Yes)
            {
                bool result = InsertNewData(cb_beams.Text, Convert.ToInt32(cb_sercal.Text) - 1, cb_station.Text, Convert.ToInt32(tb_scale.Text), Convert.ToDouble(tb_Power.Text), Convert.ToDouble(tb_length.Text));
                if (result)
                {
                    MessageBox.Show("添加成功!");
                    DisplaySerachData();
                }
                else
                {
                    MessageBox.Show("添加失败!");
                }
            }
        }

        private void btn_delete_Click(object sender, EventArgs e)
        {
            DialogResult reDialog = MessageBox.Show("是否删除数据？", "", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
            if (reDialog == System.Windows.Forms.DialogResult.Yes)
            {
                bool result = DeleteData(cb_beams.Text, Convert.ToInt32(cb_sercal.Text) - 1, cb_station.Text, Convert.ToInt32(tb_scale.Text));
                if (result)
                {
                    MessageBox.Show("删除成功!");
                    DisplaySerachData();
                }
                else
                {
                    MessageBox.Show("删除失败!");
                }
            }
        }
    }
}
